﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Cfg;
using VTMS.Model.Entities;
namespace VTMS.Access
{
    public class VehicleDao
    {
        public static string GetCurrentDate()
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                string currentDate = session.CreateSQLQuery(
                            @"SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m%d') FROM DUAL"
                            ).List()[0].ToString();
                return currentDate;
            }
        }
        public static string GetCurrentTimestamp()
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                string currentDate = session.CreateSQLQuery(
                            @"SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S') FROM DUAL"
                            ).List()[0].ToString();
                return currentDate;
            }
        }
        public static string GetLatestSerial()
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                string serial = session.CreateSQLQuery(
                            @"SELECT IF(NOT EXISTS(SELECT * FROM vtms.vehicle WHERE serial = CONCAT(DATE_FORMAT(CURRENT_DATE,'%Y%m%d'),'001')),
                            CONCAT(DATE_FORMAT(CURRENT_DATE,'%Y%m%d'),'001'),
                            (SELECT MAX(serial)+1 FROM vtms.vehicle)) as serial"
                            ).List()[0].ToString();
                return serial;
            }
        }

        public static object AddVehicle(Vehicle entity)
        {
            entity.Serial = GetLatestSerial();
            using (ISession session = SessionFactory.OpenSession())
            {
                var id = session.Save(entity);
                session.Flush();
                return id;
            }
        }

        public static void UpdateVehicle(Vehicle entity)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                session.Update(entity);
                session.Flush();
            }
        }

        public static void DeleteVehicle(Vehicle entity)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                session.Delete(entity);
                session.Flush();
            }
        }

        public static Vehicle GetBySerial(string id)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                return session.Get<Vehicle>(id);
            }
        }
        public static System.Collections.IList GetFirst20Row()
        {
            System.Collections.IList result = new System.Collections.ArrayList();
            
            using (ISession session = SessionFactory.OpenSession())
            {
                try
                {
                    result = session.CreateQuery("select Serial,OriginCustomer.Id,OriginCustomer.UserId,OriginCustomer.Name,CurrentCustomer.Id,CurrentCustomer.UserId,CurrentCustomer.Name,Vehicletype.Name,License,Brand from Vehicle order by serial desc").SetMaxResults(20).List();
                }
                catch (Exception e)
                {
                    string str = e.Message;
                }
                return result;
            }
        }
        public static System.Collections.IList SearchResult(string serial, string customerName, string customerId, string license)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                string sql = @"select Serial,OriginCustomer.Id,OriginCustomer.UserId,OriginCustomer.Name,CurrentCustomer.Id,CurrentCustomer.UserId,CurrentCustomer.Name,Vehicletype.Name,License,Brand from Vehicle where";
                if (customerName.Trim().Length > 0)
                {
                    sql += " (OriginCustomer.Name = :customerName or CurrentCustomer.Name = :customerName)";
                }
                else
                {
                    sql += " 1=1";
                }
                if (customerId.Trim().Length > 0)
                {
                    sql += " and (OriginCustomer.UserId = :customerId or CurrentCustomer.UserId = :customerId)";
                }
                else
                {
                    sql += " and 1=1";
                }
                 
                if (serial.Trim().Length > 0)
                {
                    sql += @" and serial = :serial";
                }
                if (license.Trim().Length > 0)
                {
                    sql += @" and license = :license";
                }
                
                IQuery q = session.CreateQuery(sql + " order by serial desc");
                if (customerName.Trim().Length > 0)
                {
                    q.SetParameter("customerName", customerName);
                }
                if (serial.Trim().Length > 0)
                {
                    q.SetParameter("serial", serial);
                }
                if (license.Trim().Length > 0)
                {
                    q.SetParameter("license", license);
                }
                if (customerId.Trim().Length > 0)
                {
                    q.SetParameter("customerId", customerId);
                }
                return q.List();
            }
        }
        public static System.Collections.IList CaculateCompanyReport(DateTime startDate, DateTime endDate, string company)
        {
            string sql = "select Serial,Brand,Register,Transactions,Actual,Company.Id from Vehicle where Company.Id = :company and SaveDate > :startDate and SaveDate < :endDate";
            using (ISession session = SessionFactory.OpenSession())
            {
                IQuery q = session.CreateQuery(sql);
                q.SetParameter("company", company);
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                return q.List();
            }
        }
        public static System.Collections.IList CaculateVehicleReport(DateTime startDate, DateTime endDate, string brand)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                IQuery q = session.CreateQuery("select Serial,Register,Vehicletype.Name,Brand,Transactions,Actual from Vehicle where SaveDate > :startDate and SaveDate < :endDate and brand like :brand");
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                q.SetParameter("brand", "%"+brand+"%");
                return q.List();
            }
        }
        public static System.Collections.IList CaculateTradeDailyReport(DateTime startDate, DateTime endDate, string condition)
        {
            string sqlAll = "Select SaveDate,Serial,License,Actual,Company from Vehicle where Save_Date > :startDate and Save_Date < :endDate and Charge_Date > :startDate";
            string sqlPrinted = " and isprinted = true";
            string sqlCharged = " and ischarged = true";
            string sqlOrderby = " order by charge_date";
            IQuery q = null;
            using (ISession session = SessionFactory.OpenSession())
            {
                
                if (condition.Equals("已出票"))
                {
                    q = session.CreateQuery(sqlAll + sqlPrinted + sqlOrderby);
                }
                else if (condition.Equals("已缴费"))
                {
                    sqlAll = "Select SaveDate,Serial,License,Actual,Company from Vehicle where Charge_Date > :startDate and Charge_Date < :endDate";
                    q = session.CreateQuery(sqlAll + sqlCharged + sqlOrderby);
                }
                else
                {
                    q = session.CreateQuery(sqlAll + sqlOrderby);
                }
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                return q.List();
            }
        }
        public static System.Collections.IList CaculateTradeMonthReport(DateTime startDate, DateTime endDate)
        {
            string sql = "select DATE_FORMAT(Save_Date,'%Y年%m月%d日'), sum(Actual), count(*) from Vehicle where Save_Date > :startDate and Save_Date < :endDate group by DATE_FORMAT(Save_Date,'%Y年%m月%d日') order by DATE_FORMAT(Save_Date,'%Y年%m月%d日')";
            System.Collections.IList listVehicle = new System.Collections.ArrayList();
            IQuery q = null;

            using (ISession session = SessionFactory.OpenSession())
            {
                q = session.CreateSQLQuery(sql);
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                System.Collections.IList tmp = q.List();
                foreach (object[] o in tmp)
                {
                    VirtualReport report = new VirtualReport();
                    report.SaveDate = o[0].ToString();
                    report.Money = o[1].ToString();
                    report.Count = int.Parse(o[2].ToString());
                    listVehicle.Add(report);
                }
                return listVehicle;
            }
        }
        public static System.Collections.IList CaculateTradeYearReport(DateTime startDate, DateTime endDate)
        {
            string sql = "select DATE_FORMAT(Save_Date,'%Y年%m月'), sum(Actual), count(*) from Vehicle where Save_Date > :startDate and Save_Date < :endDate group by DATE_FORMAT(Save_Date,'%Y年%m月') order by DATE_FORMAT(Save_Date,'%Y年%m月')";
            System.Collections.IList listVehicle = new System.Collections.ArrayList();
            IQuery q = null;

            using (ISession session = SessionFactory.OpenSession())
            {
                q = session.CreateSQLQuery(sql);
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                System.Collections.IList tmp = q.List();
                foreach (object[] o in tmp)
                {
                    VirtualReport report = new VirtualReport();
                    report.SaveDate = o[0].ToString();
                    report.Money = o[1].ToString();
                    report.Count = int.Parse(o[2].ToString());
                    listVehicle.Add(report);
                }
                return listVehicle;
            }
        }
        public static System.Collections.IList CaculateTaxReport(DateTime startDate, DateTime endDate)
        {
            using (ISession session = SessionFactory.OpenSession())
            {
                string sqlAll = "Select SaveDate,License,Register,Brand,Actual,Serial,OriginCustomer.Name from Vehicle where CHARACTER_LENGTH(OriginCustomer.Name) > 4 and Save_Date > :startDate and Save_Date < :endDate and Charge_Date > :startDate";
                IQuery q = session.CreateQuery(sqlAll);
                q.SetParameter("startDate", startDate);
                q.SetParameter("endDate", endDate);
                return q.List();
            }
        }
        public static System.Collections.IList GetBrands()
        {
            string sql = "select distinct brand from Vehicle";
            System.Collections.IList listVehicle = new System.Collections.ArrayList();
            IQuery q = null;

            using (ISession session = SessionFactory.OpenSession())
            {
                q = session.CreateSQLQuery(sql);
                System.Collections.IList tmp = q.List();
                foreach (object o in tmp)
                {
                    if(o != null)
                    listVehicle.Add(o.ToString());
                }
                return listVehicle;
            }
        }
    }
}
